package personal.han.service.impl;

import personal.han.dto.Column;
import personal.han.dto.TableInfo;
import personal.han.service.CompareTableService;
import personal.han.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author tanghan
 * @Description TODO
 * @date 2021/05/11/ 10:44:00
 */
public class CompareTableServiceImpl implements CompareTableService {

    private static final String BASE_DATABASE_URL = "jdbc:mysql://192.168.20.24/pcgl_v2.3?useSSL=false&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull";

    private static final String COMPARE_DATABASE_URL = "jdbc:mysql://192.168.20.24/pcgl_saas_20210510?useSSL=false&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull";

    private static final String DATABASE_USERNAME = "root";

    private static final String DATABASE_PASSWORD = "123456";


    @Override
    public List<String> compareTableInfo() {
        List<TableInfo> baseTableInfo = getTableInfo(BASE_DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD, "pcgl_v2.3", "file");
        List<TableInfo> compareTableInfo = getTableInfo(COMPARE_DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD, "pcgl_saas_20210510", "file");
        return compare(baseTableInfo, "pcgl_v2.3", compareTableInfo, "pcgl_saas_20210510");
    }

    private List<TableInfo> getTableInfo(String url,
                                         String username,
                                         String password,
                                         String catalog,
                                         String tableNamePattern) {
        Connection connection = null;
        ResultSet resultSet = null;
        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            List<String> tableNameList = new ArrayList<>();
            connection = JdbcUtils.getConnection(url, username, password);
            DatabaseMetaData metaData = connection.getMetaData();
            resultSet = metaData.getTables(catalog, null, null, new String[]{"TABLE"});
            String tableName = null;
            while (resultSet.next()) {
                tableName = resultSet.getString("TABLE_NAME");
                tableNameList.add(tableName);
            }
            for (String name : tableNameList) {
                TableInfo tableInfo = new TableInfo();
                List<Column> columnList = new ArrayList<>();
                resultSet = metaData.getColumns(catalog, null, name, null);
                while (resultSet.next()) {
                    Column column = new Column();
                    column.setColumnName(resultSet.getString("COLUMN_NAME"));
                    column.setColumnType(resultSet.getString("TYPE_NAME"));
                    column.setColumnLength(resultSet.getInt("COLUMN_SIZE"));
                    columnList.add(column);
                }
                tableInfo.setColumn(columnList);
                tableInfo.setTableName(name);
                tableInfoList.add(tableInfo);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, resultSet);
        }
        return tableInfoList;
    }

    private List<String> compare(List<TableInfo> baseTableInfo, String baseDbName, List<TableInfo> compareTableInfo,
                                 String compareDbname) {

        List<String> resultList = new ArrayList<>();

        // 表名全部转为大写，防止出现大小写不匹配
        Map<String, List<TableInfo>> baseTableMap =
                baseTableInfo.stream().collect(Collectors.groupingBy(o -> o.getTableName().toUpperCase()));
        Map<String, List<TableInfo>> compareTableMap =
                compareTableInfo.stream().collect(Collectors.groupingBy(o -> o.getTableName().toUpperCase()));

        Set<String> tableNameSet = Stream.concat(baseTableInfo.stream(), compareTableInfo.stream()).map(TableInfo::getTableName).collect(Collectors.toSet());
        for (String tableName : tableNameSet) {
            if (!baseTableMap.containsKey(tableName)) {
                resultList.add(tableName + "-在模板库中不存在");
                continue;
            }

            if (!compareTableMap.containsKey(tableName)) {
                resultList.add(tableName + "-在sass库中不存在");
                continue;
            }

            // 比较两张表差距
            TableInfo modelTable = baseTableMap.get(tableName).get(0);
            TableInfo compareTable = compareTableMap.get(tableName).get(0);
            List<Column> modelColumnList = modelTable.getColumn();
            List<Column> compareColumnList = compareTable.getColumn();

            Map<String, List<Column>> modelColumnMap = modelColumnList.stream().collect(Collectors.groupingBy(o -> o.getColumnName().toUpperCase()));
            Map<String, List<Column>> compareColumnMap = compareColumnList.stream().collect(Collectors.groupingBy(o -> o.getColumnName().toUpperCase()));

            Set<String> columnSet =
                    Stream.concat(modelColumnList.stream(), compareColumnList.stream()).map(o -> o.getColumnName().toUpperCase()).collect(Collectors.toSet());
            System.out.println(tableName);
            for (String column : columnSet) {
                if (!modelColumnMap.containsKey(column)) {
                    resultList.add(baseDbName + "库中表名为{" + tableName + "}的表不存在字段名为:{" + column + "}的字段");
                    continue;
                }
                if (!compareColumnMap.containsKey(column)) {
                    resultList.add(compareDbname + "库中表名为{" + tableName + "}的表不存在字段名为:{" + column + "}的字段");
                    continue;
                }
                Column modelCol = modelColumnMap.get(column).get(0);
                Column compareCol = compareColumnMap.get(column).get(0);
                if (!modelCol.getColumnType().equalsIgnoreCase(compareCol.getColumnType())) {
                    resultList.add("sass库和模板库中表名为{" + tableName + "}的字段类型不一致，字段为:" + column);
                }
            }
        }
        return resultList;
    }
}
